构建LangChain应用程序的示例代码：57、如何使用 MyScale 向量数据库和 LangChain 库来创建一个向量 SQL 检索器

使用 MyScale 的向量 SQL 检索器

MyScale 是一个集成的向量数据库。您可以使用 SQL 访问数据库,也可以通过 LangChain 访问。MyScale 可以利用多种数据类型和函数进行过滤。无论您是在扩展数据还是将系统扩展到更广泛的应用,它都将提升您的 LLM 应用性能。

!pip3 install clickhouse-sqlalchemy InstructorEmbedding sentence_transformers openai langchain-experimental

import getpass
from os import environ

from langchain.chains import LLMChain
from langchain.prompts import PromptTemplate
from langchain_community.utilities import SQLDatabase
from langchain_experimental.sql.vector_sql import VectorSQLDatabaseChain
from langchain_openai import OpenAI
from sqlalchemy import MetaData, create_engine

# MyScale 数据库连接信息
MYSCALE_HOST = "msc-4a9e710a.us-east-1.aws.staging.myscale.cloud"
MYSCALE_PORT = 443
MYSCALE_USER = "chatdata"
MYSCALE_PASSWORD = "myscale_rocks"
OPENAI_API_KEY = getpass.getpass("OpenAI API Key:")

# 创建数据库引擎
engine = create_engine(
    f"clickhouse://{MYSCALE_USER}:{MYSCALE_PASSWORD}@{MYSCALE_HOST}:{MYSCALE_PORT}/default?protocol=https"
)
metadata = MetaData(bind=engine)
environ["OPENAI_API_KEY"] = OPENAI_API_KEY

from langchain_community.embeddings import HuggingFaceInstructEmbeddings
from langchain_experimental.sql.vector_sql import VectorSQLOutputParser

# 创建输出解析器
output_parser = VectorSQLOutputParser.from_embeddings(
    model=HuggingFaceInstructEmbeddings(
        model_name="hkunlp/instructor-xl", model_kwargs={"device": "cpu"}
    )
)

from langchain.callbacks import StdOutCallbackHandler
from langchain_community.utilities.sql_database import SQLDatabase
from langchain_experimental.sql.prompt import MYSCALE_PROMPT
from langchain_experimental.sql.vector_sql import VectorSQLDatabaseChain
from langchain_openai import OpenAI

# 创建向量 SQL 数据库链
chain = VectorSQLDatabaseChain(
    llm_chain=LLMChain(
        llm=OpenAI(openai_api_key=OPENAI_API_KEY, temperature=0),
        prompt=MYSCALE_PROMPT,
    ),
    top_k=10,
    return_direct=True,
    sql_cmd_parser=output_parser,
    database=SQLDatabase(engine, None, metadata),
)

import pandas as pd

# 运行查询并将结果转换为 DataFrame
pd.DataFrame(
    chain.run(
        "Please give me 10 papers to ask what is PageRank?",
        callbacks=[StdOutCallbackHandler()],
    )
)

将 SQL 数据库用作检索器

from langchain.chains.qa_with_sources.retrieval import RetrievalQAWithSourcesChain
from langchain_experimental.retrievers.vector_sql_database import (
    VectorSQLDatabaseChainRetriever,
)
from langchain_experimental.sql.prompt import MYSCALE_PROMPT
from langchain_experimental.sql.vector_sql import (
    VectorSQLDatabaseChain,
    VectorSQLRetrieveAllOutputParser,
)
from langchain_openai import ChatOpenAI

# 创建检索所有输出的解析器
output_parser_retrieve_all = VectorSQLRetrieveAllOutputParser.from_embeddings(
    output_parser.model
)

# 创建向量 SQL 数据库链
chain = VectorSQLDatabaseChain.from_llm(
    llm=OpenAI(openai_api_key=OPENAI_API_KEY, temperature=0),
    prompt=MYSCALE_PROMPT,
    top_k=10,
    return_direct=True,
    db=SQLDatabase(engine, None, metadata),
    sql_cmd_parser=output_parser_retrieve_all,
    native_format=True,
)

# 创建检索器
retriever = VectorSQLDatabaseChainRetriever(
    sql_db_chain=chain, page_content_key="abstract"
)

# 创建文档元数据提示模板
document_with_metadata_prompt = PromptTemplate(
    input_variables=["page_content", "id", "title", "authors", "pubdate", "categories"],
    template="Content:\n\tTitle: {title}\n\tAbstract: {page_content}\n\tAuthors: {authors}\n\tDate of Publication: {pubdate}\n\tCategories: {categories}\nSOURCE: {id}",
)

# 创建检索 QA 链
chain = RetrievalQAWithSourcesChain.from_chain_type(
    ChatOpenAI(
        model_name="gpt-3.5-turbo-16k", openai_api_key=OPENAI_API_KEY, temperature=0.6
    ),
    retriever=retriever,
    chain_type="stuff",
    chain_type_kwargs={
        "document_prompt": document_with_metadata_prompt,
    },
    return_source_documents=True,
)

# 运行查询
ans = chain(
    "Please give me 10 papers to ask what is PageRank?",
    callbacks=[StdOutCallbackHandler()],
)
print(ans["answer"])